import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;

import java.util.*;
import java.util.zip.*;
import java.io.*;

public class PracticeInGen
{
	// variables for generate document
	// it is related to html file that is in DB
	static final String DATE = "DateHere";
	static final String INSTRUCTION = "InstructionsHere";
	static final String NUM_END = "NumEnd";
	static final String PART = "Part ";

	/**
	 *	@param args
	 */
	public static void main(String[] args)
	{	
		PracticeInGen pg = new PracticeInGen();
		try
		{
			DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@146.187.134.17:1542:oracle10g", "cscd494",	"R3tral435");
			//@machineName:port:SID, userid, password

			ArrayList<String> qCategory = new ArrayList<String>();

			qCategory = pg.getCategoryList(conn);

			//Each index in qID is an ArrayList of question_id, which correspond
			//to the index of qCategory for the name of that category
			ArrayList[] qID = new ArrayList[qCategory.size()];
			
			//initialize each array and grab the question_id
			for(int i=0;i<qCategory.size();i++)
			{
				qID[i] = new ArrayList<Integer>();
				qID[i] = pg.getCategoryQuestion(conn, qCategory.get(i));
			}
			
			pg.previewExam(conn, qCategory, qID);
			pg.createExam(conn,30);// Generate an exam question
						
			try
			{//Archive all file for exam
				ZipOutputStream zos = new ZipOutputStream(new FileOutputStream("ape.zip"));
				pg.createZip("Solution",zos);
				pg.createZip("Student",zos);
				zos.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			
			//sg.uploadToDB(conn,"ape.zip");
			conn.close();//Close database connection
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
		}
	
	}

	/**
	 * Grab a list of all the question type that question should be generated for.
	 * @param conn A database connection
	 * @return An ArrayList of all current type(category) of question available
	 */
	public ArrayList<String> getCategoryList(Connection conn)
	{
		ArrayList<String> array = new ArrayList<String>();
		try
		{
			Statement quesType = conn.createStatement();
			ResultSet rset = quesType.executeQuery("SELECT TYPE_NAME FROM QUESTION_TYPE");
			while(rset.next())
			{
				array.add(rset.getString("TYPE_NAME"));
			}
			quesType.close();
			rset.close();
		}
		catch(SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return array;
	}

	/**
	 * This will return list of question_id for a type of question passed in.
	 * @param conn	A database connection
	 * @param questionCode	The type(category) of question to grab from database
	 * @return An ArrayList of all question_id for the type(category) specified by questionCode
	 */
	public ArrayList<Integer> getCategoryQuestion(Connection conn, String questionCode)
	{
		ArrayList<Integer> store = new ArrayList<Integer>();
		try
		{
			PreparedStatement quesType = conn.prepareStatement("Select question_id from question, question_type where question.type_code = question_type.type_code and type_name = ?");
			quesType.setString(1,questionCode);
			ResultSet r = quesType.executeQuery();
			while(r.next())
			{
				store.add(r.getInt("QUESTION_ID"));
			}
			r.close();//close the ResultSet
			quesType.close();//close Statement
		}
		catch(SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return store;
	}

	/**
	 * Generate a list a potential question for an exam
	 * @param conn	A database connection
	 * @param qCategory	A list of question category
	 * @param qID	A list of question id
	 */
	public void previewExam(Connection conn, ArrayList<String> qCategory, ArrayList[] qID)
	{
		ArrayList<Integer> examQuestion = new ArrayList<Integer>();
		Random randGen = new Random();
		int randNum = 0;
		
		for(int i=0;i<qCategory.size();i++)
		{
			System.out.printf(qCategory.get(i));
			randNum = randGen.nextInt(qID[i].size());
			examQuestion.add((Integer)qID[i].get(randNum));
			
			System.out.println(" - - - " + examQuestion.get(i));
		}
		try
		{
			PreparedStatement uploadStmt = conn.prepareStatement("Insert INTO EXAM_QUESTIONS VALUES(?,?)");
			uploadStmt.setInt(1,10);
			for(int i=0;i<examQuestion.size();i++)
			{
				uploadStmt.setInt(2,(Integer)examQuestion.get(i));
				uploadStmt.execute();
			}

		}
		catch(SQLException sqlError)
		{
			sqlError.printStackTrace();
		}
	}

	/**
	 * Grab all the file need for each questionID from the list
	 * @param conn A connection to the database
	 * @param questionID An ArrayList of question_id on this exam
	 */
	public void createExam(Connection conn, int examID)//, ArrayList<Integer> questionID)
	{
		ArrayList<Integer> examQuestion = new ArrayList<Integer>();
		
		/*int[] array={11,21,31};		
		for(int i=0;i<3;i++)
		{
			String directory = "Part"+(i+1);
			new File("Student/" + directory).mkdirs();
			new File("Solution/" + directory).mkdirs();
			generateQuestion(conn,array[i], directory);
		}*/
		try
		{
			PreparedStatement pstmt = conn.prepareStatement("SELECT QUESTION_ID FROM EXAM_QUESTIONS WHERE EXAM_ID=?");
			pstmt.setInt(1,examID);
			ResultSet rset = pstmt.executeQuery();
			
			while(rset.next())
			{
				examQuestion.add(rset.getInt("QUESTION_ID"));
			}

			for(int i=0;i<examQuestion.size();i++)
			{
				String directory = "Part"+(i+1);
				new File("Student/" + directory).mkdirs();
				new File("Solution/" + directory).mkdirs();
				generateQuestion(conn,examQuestion.get(i), directory);
			}
			
			generateJar(conn);
			generateInstruction(conn, examQuestion, examQuestion.size());
		}
		catch(SQLException sqlError)
		{
			sqlError.printStackTrace();
		}
	}

	/**
	 * Grab all the file need to generate a question. Eventually it will accept a question_id to generate more different question.
	 * @param conn A database connection
	 */
	public void generateQuestion(Connection conn, int qID, String pDirectory)
	{
	}
/*
		try
		{
		
			PreparedStatement qOutput = conn.prepareStatement("SELECT QUESTION.QUESTION_OUTPUT FROM QUESTION WHERE QUESTION_ID =?");
			qOutput.setInt(1,qID);
			ResultSet qResult = qOutput.executeQuery();
			
			qResult.next();
			Blob blob1 = qResult.getBlob("QUESTION_OUTPUT");
			BufferedOutputStream sStream = new BufferedOutputStream(new FileOutputStream(new File("Student/" + pDirectory, "sampleOutput.txt")));
			sStream.write(blob1.getBytes(1, (int)blob1.length()), 0, (int)blob1.length());//Write out the length of blob to file
			sStream.flush();
			sStream.close();
			qResult.close();
			qOutput.close();
			
			//SELECT COUNT(question_id) AS Total FROM EXAM_QUESTIONS WHERE exam_id = 10;
			PreparedStatement prepStatement = conn.prepareStatement("SELECT QUESTION_SOURCE_FILE.source_file_id AS \"File ID\", SOURCE_FILE.file_name AS \"File Name\",SOURCE_FILE.source_file AS \"Source\" FROM QUESTION_SOURCE_FILE INNER JOIN SOURCE_FILE ON QUESTION_SOURCE_FILE.source_file_id = SOURCE_FILE.source_file_id WHERE question_id = ?");
			prepStatement.setInt(1,qID);//Change question_id
			ResultSet rset = prepStatement.executeQuery(); //run PreparedStatement

			while	(rset.next())//While the result is not empty
			{		
				String filename =	rset.getString("File Name");
				
				if(filename.toLowerCase().contains(".java"))//Check if the file is .Java
				{
					Scanner scan =	new Scanner(rset.getBinaryStream("Source"));

					PrintWriter	studentFile	= new	PrintWriter(new File("Student/"+pDirectory,filename));
					PrintWriter	solutionFile =	new PrintWriter(new File("Solution/"+pDirectory,filename));
					while(scan.hasNext())
					{
						String curLine	= scan.nextLine();
						if(curLine.equals("//ans bl"))
						{
							curLine = scan.nextLine();
							while(!curLine.equals("//ans ab"))
							{
								solutionFile.println(curLine);
								curLine = scan.nextLine();
							}
						}
						else
						{
							studentFile.println(curLine);
							solutionFile.println(curLine);
						}
					}
					studentFile.close();
					solutionFile.close();
				}
				else// if(filename.indexOf(".java")<0)//Not a java file don't need to parse
				{
					Blob blob = rset.getBlob("Source");
					BufferedOutputStream studentStream = new BufferedOutputStream(new FileOutputStream(new File("Student/" + pDirectory, filename)));
					BufferedOutputStream solutionStream = new BufferedOutputStream(new FileOutputStream(new File("Solution/" + pDirectory, filename)));

					studentStream.write(blob.getBytes(1, (int)blob.length()), 0, (int)blob.length());//Write out the length of blob to file
					solutionStream.write(blob.getBytes(1, (int)blob.length()), 0, (int)blob.length());

					studentStream.flush();
					solutionStream.flush();

					studentStream.close();
					solutionStream.close();
				}
			}
			rset.close();//Close ResultSet
			prepStatement.close();//Close PreparedStatement
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
*/

	public void generateJar(Connection conn)
	{
		try
		{
			Scanner inFile = new Scanner(new File("jar_instructions.html"));//later read from DB
			PrintWriter outFile = new PrintWriter(new File("Student/jar_instructions.html"));
			
			//when read from DB, read it as blob and just spit out to html because there is nothing to change
			while(inFile.hasNext())
			{
				outFile.println(inFile.nextLine());
			}
			inFile.close();
			outFile.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * Grab all the instructions that are under questions used this exam.
	 * It will be attached to pre-saved document in DB
	 * @param conn A database connection
	 */
	public void generateInstruction(Connection conn, ArrayList<Integer> examQuestion, int numQ)
	{
		try
		{
			Scanner inFile = new Scanner(new File("exam_instructions.html"));//later read from DB
			PrintWriter outFile = new PrintWriter(new File("Student/exam_instructions.html"));
			
			while(inFile.hasNext())
			{
				String s = inFile.nextLine();
				if(s.contains(DATE))
				{
					s = s.replace(DATE, "Read Exam Date From DB");
				}
				else if(s.contains(NUM_END))
				{
					s = s.replace(NUM_END, ((Integer)numQ).toString());
				}
				else if(s.contains(INSTRUCTION))
				{
					s = "";
					for(int i=1;i<=examQuestion.size();i++)
					{
						s += "<li><u>" + PART + i + "</u>:&nbsp&nbsp";
						s += readInstruction(conn, examQuestion.get(i-1));
						s += "</li><br><br>";
					}
				}
				outFile.println(s);
			}
			inFile.close();
			outFile.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public String readInstruction(Connection conn, int qID)
	{
		String instruction = "";
		try
		{
			//after instruction saved as blob, change it to from QUESTION_TEXT
			PreparedStatement qOutput = conn.prepareStatement("SELECT QUESTION.QUESTION_SOURCE FROM QUESTION WHERE QUESTION_ID =?");
			qOutput.setInt(1,qID);
			ResultSet qResult = qOutput.executeQuery();
			
			qResult.next();
			//after instruction saved as blob, change it to from QUESTION_TEXT
			Blob b = qResult.getBlob("QUESTION_SOURCE");
			instruction = new String(b.getBytes(1, (int)b.length()));
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return instruction;
	}

	/**
	 * Create a zip file with the content of the directory passed in.
	 * @param dir	The directory to be zip
	 * @param zos	A ZipOutputStream where files will be written
	 * http://www.devx.com/tips/Tip/14049
	 */
	public void createZip(String dir, ZipOutputStream zos)
	{
		try
		{
			File zipDir = new File(dir);
			String[] dirList = zipDir.list();
			byte[] buffer = new byte[1000];
			int bytesIn = 0;
			
			for(int i=0;i<dirList.length;i++)
			{
				File f = new File(zipDir,dirList[i]);
				if(f.isDirectory())
				{
					String filePath = f.getPath();
					createZip(filePath, zos);
					
					continue;
				}
				
				FileInputStream fis = new FileInputStream(f);
				ZipEntry entry = new ZipEntry(f.getPath());
				zos.putNextEntry(entry);
				while((bytesIn = fis.read(buffer)) != -1)
				{
					zos.write(buffer, 0, bytesIn);
				}
				fis.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}
	
	/**
	 * Upload an archive with all data related to an exam.
	 * @param conn	A database connection
	 * @param filename	Name of file to upload
	 */
	public void uploadToDB(Connection conn, String filename)
	{
		try
		{
			PreparedStatement uploadStmt = conn.prepareStatement("Insert INTO BTAB FILE_NAME VALUES(?,?,?)");
			
			File blob = new File(filename);
			FileInputStream is = new FileInputStream(blob);
			uploadStmt.setInt(1, 1);//Set the ID
			uploadStmt.setBinaryStream(2, is, (int)blob.length());//set file upload stream
			uploadStmt.setString(3,filename);//set the filename
			uploadStmt.execute();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
}
